package vn.candyapp.qlct.database;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

import vn.candyapp.qlct.database.model.Account;
import vn.candyapp.qlct.database.model.AccountList;
import vn.candyapp.qlct.database.model.Category;
import vn.candyapp.qlct.database.model.CategoryList;
import vn.candyapp.qlct.database.model.PaymentStatus;
import vn.candyapp.qlct.database.model.PaymentStatusList;
import vn.candyapp.qlct.database.model.Table;
import vn.candyapp.qlct.database.model.ThongKeDS;
import vn.candyapp.qlct.database.model.ThongKeItem;
import vn.candyapp.qlct.database.model.Transaction;
import vn.candyapp.qlct.database.model.TransactionType;
import vn.candyapp.qlct.database.model.TransactionTypeList;
import vn.candyapp.qlct.item.KieuTKGiaoDich;
import vn.candyapp.qlct.utils.DateTimeUtil;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

public class DBAdapter extends SQLiteHelper {

	public DBAdapter(Context context) {
		super(context);
	}
	
	@Override
	public CategoryList getAllCategories() {
		CategoryList cl = new CategoryList();
		ArrayList<Category> categories = new ArrayList<Category>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM "+Table.TABLE_CATEGORY+"", null);
		if (c.moveToFirst()) {
			do {
				Category cat = new Category();
				cat.setId(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_ID)));
				cat.setName(c.getString(c.getColumnIndex(Table.KEY_CATEGORY_NAME)));
				cat.setDescription(c.getString(c.getColumnIndex(Table.KEY_CATEGORY_DESCRIPTION)));
				cat.setOrderNo(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_ORDER_NO)));
				cat.setColor(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_COLOR)));
				cat.setTransactionType(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_TRANSACTION_TYPE)));
				categories.add(cat);
			} while(c.moveToNext());
		}
		c.close();
		if (db != null && db.isOpen()) db.close();
		cl.setCategoriesList(categories);
		return cl;
	}

	@Override
	public CategoryList getAllCategories(int transType) {
		CategoryList cl = new CategoryList();
		ArrayList<Category> categories = new ArrayList<Category>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM "+Table.TABLE_CATEGORY+" WHERE transaction_type="+transType, null);
		if (c.moveToFirst()) {
			do {
				Category cat = new Category();
				cat.setId(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_ID)));
				cat.setName(c.getString(c.getColumnIndex(Table.KEY_CATEGORY_NAME)));
				cat.setDescription(c.getString(c.getColumnIndex(Table.KEY_CATEGORY_DESCRIPTION)));
				cat.setOrderNo(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_ORDER_NO)));
				cat.setColor(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_COLOR)));
				cat.setTransactionType(c.getInt(c.getColumnIndex(Table.KEY_CATEGORY_TRANSACTION_TYPE)));
				categories.add(cat);
			} while(c.moveToNext());
		}
		c.close();
		if (db != null && db.isOpen()) db.close();
		cl.setCategoriesList(categories);
		return cl;
	}

	@Override
	public TransactionTypeList getAllTransactionType() {
		TransactionTypeList ttl = new TransactionTypeList();
		ArrayList<TransactionType> data = new ArrayList<TransactionType>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.query(Table.TABLE_TRANSACTION_TYPE, new String[] { Table.KEY_TRANSACTION_TYPE_ID, Table.KEY_TRANSACTION_TYPE_NAME, Table.KEY_TRANSACTION_TYPE_IS_DEFAULT, Table.KEY_TRANSACTION_TYPE_TYPE_NAME }, null, null, null, null, null);
		if (c.moveToFirst()) {
			do {
				TransactionType type = new TransactionType();
				type.setId(c.getInt(c.getColumnIndex(Table.KEY_TRANSACTION_TYPE_ID)));
				type.setName(c.getString(c.getColumnIndex(Table.KEY_TRANSACTION_TYPE_NAME)));
				type.setDefault(c.getInt(c.getColumnIndex(Table.KEY_TRANSACTION_TYPE_IS_DEFAULT)) == 1);
				type.setTypeName(c.getString(c.getColumnIndex(Table.KEY_TRANSACTION_TYPE_TYPE_NAME)));
				data.add(type);
			} while(c.moveToNext());
		}
		c.close();
		if (db != null && db.isOpen()) db.close();
		ttl.setTransactionTypeList(data);
		return ttl;
	}

	@Override
	public PaymentStatusList getPaymentStatus() {
		PaymentStatusList data = new PaymentStatusList();
		ArrayList<PaymentStatus> paymentStatusList = new ArrayList<PaymentStatus>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM "+Table.TABLE_PAYMENT_STATUS, null);
		if (c.moveToFirst()) {
			do {
				PaymentStatus item = new PaymentStatus();
				item.setId(c.getInt(c.getColumnIndex(Table.KEY_PAYMENT_STATUS_ID)));
				item.setName(c.getString(c.getColumnIndex(Table.KEY_PAYMENT_STATUS_NAME)));
				item.setDescription(c.getString(c.getColumnIndex(Table.KEY_PAYMENT_STATUS_DESCRIPTION)));
				item.setCompleted(c.getInt(c.getColumnIndex(Table.KEY_PAYMENT_STATUS_IS_COMPLETED)));
				item.setDefault(c.getInt(c.getColumnIndex(Table.KEY_PAYMENT_STATUS_IS_DEFAULT)));
				item.setOrderNo(c.getInt(c.getColumnIndex(Table.KEY_PAYMENT_STATUS_ORDER_NO)));
				paymentStatusList.add(item);
			} while(c.moveToNext());
		}
		c.close();
		if (db != null && db.isOpen()) db.close();
		data.setPaymentStatusList(paymentStatusList);
		return data;
	}

	@Override
	public AccountList getAccounts() {
		AccountList accList = new AccountList();
		ArrayList<Account> arrAccount = new ArrayList<Account>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = db.rawQuery("SELECT * FROM "+Table.TABLE_ACCOUNT, null);
		if (c.moveToFirst()) {
			do {
				Account account = new Account();
				account.setId(c.getInt(c.getColumnIndex(Table.KEY_ACCOUNT_ID)));
				account.setName(c.getString(c.getColumnIndex(Table.KEY_ACCOUNT_NAME)));
				account.setCurrency(c.getString(c.getColumnIndex(Table.KEY_ACCOUNT_CURRENCY)));
				account.setCreatedDate(c.getString(c.getColumnIndex(Table.KEY_ACCOUNT_CREATED_DATE)));
				account.setIsCreditCard(c.getInt(c.getColumnIndex(Table.KEY_ACCOUNT_IS_CREDIT_CARD)));
				account.setBalance(c.getFloat(c.getColumnIndex(Table.KEY_ACCOUNT_BALANCE)));
				arrAccount.add(account);
			} while(c.moveToNext());
		}
		c.close();
		if (db != null && db.isOpen()) db.close();
		accList.setAccountList(arrAccount);
		return accList;
	}

	
	@Override
	public long insertNewTransaction(ContentValues values) {
		SQLiteDatabase db = getWritableDatabase();
		if (db.isOpen()) {
			return db.insert(Table.TABLE_TRANSACTION, null, values);
		}
		if (db != null && db.isOpen()) db.close();
		return -1;
	}
	
	public void updateAccount(int accountId, String transactionTypeName, float amount) {
		SQLiteDatabase db = getWritableDatabase();
		float v = "expense".equals(transactionTypeName) ? -amount : amount;
		db.execSQL("UPDATE "+Table.TABLE_ACCOUNT+" SET balance=balance+"+v+" WHERE id="+accountId);
		if (db != null && db.isOpen()) db.close();
	}
	
	@Override
	public long addNewTransaction(Transaction trans) {
		
		SQLiteDatabase db = getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(Table.KEY_TRANSACTION_CATEGORY_ID, trans.getCategoryId());
		values.put(Table.KEY_TRANSACTION_AMOUNT, trans.getAmount());
		values.put(Table.KEY_TRANSACTION_PAYMENT_STATUS, trans.getPaymentStatus());
		values.put(Table.KEY_TRANSACTION_DESCRIPTION, trans.getDescription());
		values.put(Table.KEY_TRANSACTION_CREATED_DATE, trans.getCreatedDate());
		values.put(Table.KEY_TRANSACTION_TRANSACTION_TYPE, trans.getTransactionType());
		values.put(Table.KEY_TRANSACTION_ACCOUNT_ID, trans.getAccountId());
		
		long affectedId = db.insert(String.format("\"%s\"", Table.TABLE_TRANSACTION), null, values);
		if (db != null && db.isOpen()) db.close();
		updateAccount(trans.getAccountId(), trans.getTransactionTypeName(), trans.getAmount());
		return affectedId; 
	}

	@Override
	public List<Transaction> getAllTransaction(Date date, KieuTKGiaoDich dType) {
		List<Transaction> list = new ArrayList<Transaction>();
		SQLiteDatabase db = getReadableDatabase();
		String sql = "SELECT "+Table.TABLE_TRANSACTION+".*, "+Table.TABLE_ACCOUNT+".name, "+Table.TABLE_TRANSACTION_TYPE+".type_name, "+Table.TABLE_CATEGORY+".name as categoryName FROM " + Table.TABLE_CATEGORY+", " + Table.TABLE_TRANSACTION + ", " + Table.TABLE_ACCOUNT+", " + Table.TABLE_TRANSACTION_TYPE+" WHERE " + Table.TABLE_TRANSACTION+"."+Table.KEY_TRANSACTION_ACCOUNT_ID+"="+Table.TABLE_ACCOUNT+"."+Table.KEY_ACCOUNT_ID; 
		sql += " AND "+Table.TABLE_TRANSACTION+".transaction_type = "+Table.TABLE_TRANSACTION_TYPE+".id AND "+Table.TABLE_CATEGORY+".id="+Table.TABLE_TRANSACTION+".category_id";
		switch(dType) {
			case HOMNAY:
				sql += " AND strftime('%Y-%m-%d', "+Table.TABLE_TRANSACTION+"."+Table.KEY_TRANSACTION_CREATED_DATE + ") = '" + DateTimeUtil.getNgayThangNam(date, "yyyy-MM-dd") + "'";
				break;
			case TUANNAY:
				Date fow = DateTimeUtil.getFirstDayOfWeek(date);
				Date eow = DateTimeUtil.getNextDayOfLastDayOfWeek(date);
				sql += " AND "+Table.TABLE_TRANSACTION+"."+Table.KEY_TRANSACTION_CREATED_DATE+" >= Datetime('" + DateTimeUtil.getNgayThangNam(fow, "yyyy-MM-dd") + "') AND "+Table.TABLE_TRANSACTION+"."+Table.KEY_TRANSACTION_CREATED_DATE+" < Datetime('" +DateTimeUtil.getNgayThangNam(eow, "yyyy-MM-dd") + "')"; 
				break;
			case NAMNAY:
				sql += " AND strftime('%Y', "+Table.TABLE_TRANSACTION+"."+Table.KEY_TRANSACTION_CREATED_DATE + ") = '" + DateTimeUtil.getNgayThangNam(date, "yyyy") + "'";
				break;
			case THANGNAY:
				sql += " AND strftime('%Y-%m', "+Table.TABLE_TRANSACTION+"."+Table.KEY_TRANSACTION_CREATED_DATE + ") = '" + DateTimeUtil.getNgayThangNam(date, "yyyy-MM") + "'";
				break;
		}
		Cursor c = null;
		try {
			c = db.rawQuery(sql, null);
			if (c.moveToFirst()) {
				do {
					Transaction tran = new Transaction();
					tran.setId(c.getInt(c.getColumnIndex(Table.KEY_TRANSACTION_ID)));
					tran.setAmount(c.getFloat(c.getColumnIndex(Table.KEY_TRANSACTION_AMOUNT)));
					tran.setDescription(c.getString(c.getColumnIndex(Table.KEY_TRANSACTION_DESCRIPTION)));
					tran.setPaymentStatus(c.getInt(c.getColumnIndex(Table.KEY_TRANSACTION_PAYMENT_STATUS)));
					tran.setTransactionType(c.getInt(c.getColumnIndex(Table.KEY_TRANSACTION_TRANSACTION_TYPE)));
					tran.setCreatedDate(c.getString(c.getColumnIndex(Table.KEY_TRANSACTION_CREATED_DATE)));
					tran.setAccountName(c.getString(c.getColumnIndex(Table.KEY_ACCOUNT_NAME)));
					tran.setAccountId(c.getInt(c.getColumnIndex(Table.KEY_TRANSACTION_ACCOUNT_ID)));
					tran.setTransactionTypeName(c.getString(c.getColumnIndex(Table.KEY_TRANSACTION_TYPE_TYPE_NAME)));
					tran.setCategoryName(c.getString(c.getColumnIndex("categoryName")));
					list.add(tran);
				} while(c.moveToNext());
			}
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) c.close();
			if (db != null && db.isOpen()) db.close();
		}
		return list;
	}

	@Override
	public ThongKeDS[] getThongKe(Date d, KieuTKGiaoDich dType) {
		ThongKeDS[] data = new ThongKeDS[2];
		ThongKeDS dsThu = new ThongKeDS("thu");
		ThongKeDS dsChi = new ThongKeDS("chi");
		
		SQLiteDatabase db = getReadableDatabase();
		String sql = "SELECT category.name, category.transaction_type, " +
							"(SELECT sum(da_transaction.amount) FROM da_transaction WHERE da_transaction.category_id = category.id AND ";
		
		switch (dType) {
			case THANGNAY:
				sql += "strftime('%Y-%m', da_transaction.created_date)='" + DateTimeUtil.getNgayThangNam(d, "yyyy-MM") + "'";
				break;
			case TUANNAY:
				Date fow = DateTimeUtil.getFirstDayOfWeek(d);
				Date eof = DateTimeUtil.getNextDayOfLastDayOfWeek(d);
				
				sql += "da_transaction.created_date >=Datetime('" + DateTimeUtil.getNgayThangNam(fow, "yyyy-MM-dd") + "') AND  da_transaction.created_date < Datetime('" + DateTimeUtil.getNgayThangNam(eof, "yyyy-MM-dd") + "')";
				
				break;
			case NAMNAY:
				sql += "strftime('%Y', da_transaction.created_date)='" + DateTimeUtil.getNgayThangNam(d, "yyyy") + "'";
				break;
			default:
				 sql += "strftime('%Y-%m-%d', da_transaction.created_date)='" + DateTimeUtil.getNgayThangNam(d, "yyyy-MM-dd") + "'";
				break;
		}
		sql += ") as total FROM category WHERE total > 0";
		
		Cursor c = null;
		try {
			c =db.rawQuery(sql, null);
			if (c.moveToFirst()) {
				do {
					ThongKeItem tkItem = new ThongKeItem(c.getString(c.getColumnIndex(ThongKeItem.KEY_CATEGORY_NAME)), c.getFloat(c.getColumnIndex(ThongKeItem.KEY_CATEGORY_TOTAL)), c.getInt(c.getColumnIndex(ThongKeItem.KEY_CATEGORY_TYPE)));
					if (tkItem.getTransactionType() == 1) {
						dsChi.addCategoryItem(tkItem);
					} else if (tkItem.getTransactionType() == 2) {
						dsThu.addCategoryItem(tkItem);
					}
				} while(c.moveToNext());
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) c.close();
			if (db != null && db.isOpen()) db.close();
		}
		data[0] = dsThu;
		data[1] = dsChi;
		return data;
	}

	@Override
	public long addNewCategory(String name) {
		
		SQLiteDatabase db = getWritableDatabase();
		if (db != null && db.isOpen()) {
			ContentValues cv = new ContentValues();
			cv.put(Table.KEY_ACCOUNT_NAME, name);
			db.insert(Table.TABLE_ACCOUNT, null, cv);
			db.close();
		}
		return 0;
	}

	@Override
	public long addCategory(String name, int type) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null && db.isOpen()) {
			ContentValues cv = new ContentValues();
			cv.put("transaction_type", type);
			cv.put("name", name);
			db.insert(Table.TABLE_CATEGORY, null, cv);
			db.close();
		}
		return 0;
	}

	@Override
	public void deleteCategory(int id) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null && db.isOpen()) {
			db.execSQL("DELETE FROM "+Table.TABLE_CATEGORY+" WHERE id="+id);
			db.close();
		}
	}

	@Override
	public void deleteAccount(int id) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null && db.isOpen()) {
			db.execSQL("DELETE FROM "+Table.TABLE_ACCOUNT+" WHERE id="+id);
			db.close();
		}
	}

	@Override
	public void deleteTransaction(Transaction tran) {
		SQLiteDatabase db = getWritableDatabase();
		if (db != null && db.isOpen()) {
			db.execSQL("DELETE FROM "+Table.TABLE_TRANSACTION+" WHERE id="+tran.getId());
			float v = "expense".equals(tran.getTransactionTypeName()) ? tran.getAmount() : -tran.getAmount();
			db.execSQL("UPDATE "+Table.TABLE_ACCOUNT+" SET balance=balance+"+v+" WHERE id="+tran.getAccountId());
			db.close();
		}
	}

}
